﻿using System;
using System.Data;
using TopBuilds.Database;

namespace TopBuilds.Restaurant.Data
{
    public class RoomObject
    {
        public static Room[] GetRooms()
        {
            return Query(null);
        }

        public static Room GetRoom(int id)
         {
             Room room = new Room();

             SqlBuilder builder = new SqlBuilder();
             builder.ConditionAdd(RoomDesc.Id, ConditionExpress.EQUALS, id);
             builder.QueryTableName = RoomDesc.TableName;
             string sql = builder.QueryCommandLine;

             DataTable rooms = DatabaseObject.Instance.ExecuteQuerySQL(sql);

             room.Id                        = Convert.ToInt32(rooms.Rows[0][RoomCategoryDesc.Id]);
             room.RoomCategoryId            = Convert.ToInt32(rooms.Rows[0][RoomDesc.RoomCategoryId]);
             room.Name                      = Convert.ToString(rooms.Rows[0][RoomDesc.Name]);
             room.MinConsumption            = Convert.ToInt32(rooms.Rows[0][RoomDesc.MinConsumption]);
             room.State                     = Convert.ToInt32(rooms.Rows[0][RoomDesc.State]);
             room.Remark                    = Convert.ToString(rooms.Rows[0][RoomDesc.Remark]);

             return room;
         }

        public static Room GetRoom(string name)
        {
            SqlBuilder builder = new SqlBuilder();
            builder.ConditionAdd(RoomDesc.Name, ConditionExpress.EQUALS, name);

            Room[] set = Query(builder.ConditionString);
            if (set != null && set.Length > 0)
            {
                return set[0];
            }

            return null;
        }

        public static Room[] Query(string condition)
         {

             // query factories from database
             string sql = "select * from " + RoomDesc.TableName;
             if (condition != null && condition.Length > 0)
             {
                 sql += " where ";
             }
             DataTable table = DatabaseObject.Instance.ExecuteQuerySQL(sql);


             // rebuild the data

             if (table != null && table.Rows.Count > 0)
             {
                 Room[] rooms = new Room[table.Rows.Count];

                 for (int i = 0; i < table.Rows.Count; i++)
                 {
                     Room room = new Room();

                     room.Id                    = Convert.ToInt32(table.Rows[i][RoomCategoryDesc.Id]);
                     room.RoomCategoryId        = Convert.ToInt32(table.Rows[i][RoomDesc.RoomCategoryId]);
                     room.Name                  = Convert.ToString(table.Rows[i][RoomDesc.Name]);
                     room.MinConsumption        = Convert.ToInt32(table.Rows[i][RoomDesc.MinConsumption]);
                     room.State                 = Convert.ToInt32(table.Rows[i][RoomDesc.State]);
                     room.Remark                = Convert.ToString(table.Rows[i][RoomDesc.Remark]);


                     rooms.SetValue(room, i);
                 }

                 return rooms;
             }
             return null;
         }

         public static Int32 Add(Room room)
         {
             SqlBuilder builder = new SqlBuilder();
             builder.InsertAdd(RoomDesc.RoomCategoryId,                 room.RoomCategoryId);
             builder.InsertAdd(RoomDesc.Name,                           room.Name);
             builder.InsertAdd(RoomDesc.MinConsumption,                 room.MinConsumption);
             builder.InsertAdd(RoomDesc.State,                          room.State);
             builder.InsertAdd(RoomDesc.Remark,                         room.Remark);
             builder.QueryTableName = RoomDesc.TableName;

             string sql = builder.InsertCommandLine;

             return DatabaseObject.Instance.ExecuteNonQuerySQL(sql);
         }

         public static bool Delete(Int32 id)
         {
             SqlBuilder builder = new SqlBuilder();
             builder.ConditionAdd(RoomDesc.Id, ConditionExpress.EQUALS, id);
             builder.QueryTableName = RoomDesc.TableName;
             string sql = builder.DeleteCommandLine;

             return (DatabaseObject.Instance.ExecuteNonQuerySQL(sql) != -1);
         }

         public static Int32 Update(Room room)
         {
             SqlBuilder builder = new SqlBuilder();
             builder.UpdateAdd(RoomDesc.RoomCategoryId,           room.RoomCategoryId);
             builder.UpdateAdd(RoomDesc.Name,                     room.Name);
             builder.UpdateAdd(RoomDesc.MinConsumption,           room.MinConsumption);
             builder.UpdateAdd(RoomDesc.State,                    room.State);
             builder.UpdateAdd(RoomDesc.Remark,                   room.Remark);
             builder.ConditionAdd(RoomDesc.Id, ConditionExpress.EQUALS, room.Id);
             builder.QueryTableName = RoomDesc.TableName;

             string sql = builder.UpdateCommandLine;

             return DatabaseObject.Instance.ExecuteNonQuerySQL(sql);
         }
    }
}
